package unilearn.da;

import java.sql.*;
import java.util.Vector;
import javax.naming.*;

import unilearn.domain.Assessment;

/**
 * @author Yin Zhang, 5393558
 * @author Ben Morrall, 4158768
 * @version 0.1, 30 April 2008, initial creation
 * @version 0.2, 14 May 2008, Refactored Code, blm
 * @version 0.3, 14 May 2008, Extracted SQL to Constants, blm
 */
public class DAAssessment extends DABase
{
	private static final String SQL_GET_ASSESSMENT = "SELECT * FROM assessment WHERE assessment_id = ?";
	private static final String SQL_GET_ASSESSMENT_STAFF = "SELECT * FROM assessment WHERE username = ?";
	private static final String SQL_GET_ASSESSMENT_SUBMISSION = "SELECT * FROM assessment WHERE submission_id = ?";
	private static final String SQL_INSERT_ASSESSMENT = "INSERT INTO assessment"
			+ "(submission_id, assessment_date, assessor_remarks, grade, username) "
			+ "VALUES (?,?,?,?,?)";
	private static final String SQL_UPDATE_ASSESSMENT = "UPDATE assessment SET "
			+ "submission_id = ?, assessment_date = ?, assessor_remarks = ?, grade = ?, username = ? "
			+ "WHERE assessment_id = ?";

	/**
	 * Generates an Assessment from a ResultSet
	 * @param rs ResultSet containing Assessment
	 * @return generated Assessment
	 * @throws SQLException
	 */
	private static Assessment generateAssessment(ResultSet rs) throws SQLException
	{
		Assessment assessment;
		assessment = new Assessment();
		assessment.setAssessmentID(rs.getLong("assessment_id"));
		assessment.setSubmissionID(rs.getLong("submission_id"));
		assessment.setAssessmentDate(rs.getTimestamp("assessment_date"));
		assessment.setComment(rs.getString("assessor_remarks"));
		assessment.setGrade(rs.getInt("grade"));
		assessment.setStaffID(rs.getString("username"));
		return assessment;
	}

	public static Assessment getAssessmentByID(long assessmentID) throws SQLException
	{
		Assessment assessment = null;
		PreparedStatement ps = null;
		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement(SQL_GET_ASSESSMENT);
			ps.setLong(1, assessmentID);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				assessment = generateAssessment(rs);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assessment;
	}

	public static Vector<Assessment> getAssessmentByStaff(String staffID) throws SQLException
	{
		Vector<Assessment> assessments = new Vector<Assessment>();
		PreparedStatement ps = null;
		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement(SQL_GET_ASSESSMENT_STAFF);
			ps.setString(1, staffID);

			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				Assessment assessment = generateAssessment(rs);
				assessments.add(assessment);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assessments;
	}

	public static Assessment getAssessmentBySubmission(long submissionID)
		throws SQLException, NamingException
	{
		Assessment assessment = null;
		PreparedStatement ps = null;
		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement(SQL_GET_ASSESSMENT_SUBMISSION);
			ps.setLong(1, submissionID);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				assessment = generateAssessment(rs);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assessment;
	}

	public static long insertAssessment(Assessment assessment) throws SQLException,
		NamingException
	{
		long assessmentID = -1;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement(SQL_INSERT_ASSESSMENT, Statement.RETURN_GENERATED_KEYS);
			ps.setLong(1, assessment.getSubmissionID());
			ps.setTimestamp(2, assessment.getAssessmentDate());
			ps.setString(3, assessment.getComment());
			ps.setDouble(4, assessment.getGrade());
			ps.setString(5, assessment.getStaffID());

			ps.execute();

			ResultSet results = ps.getGeneratedKeys();

			if (results.next())
			{
				assessmentID = results.getLong(1);
				assessment.setAssessmentID(assessmentID);
			}
			else
			{
				throw new SQLException("Unable to create Assessment");
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return assessmentID;
	}

	public static void updateAssessment(Assessment assessment) throws SQLException,
		NamingException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement(SQL_UPDATE_ASSESSMENT);

			ps.setLong(1, assessment.getSubmissionID());
			ps.setTimestamp(2, assessment.getAssessmentDate());
			ps.setString(3, assessment.getComment());
			ps.setDouble(4, assessment.getGrade());
			ps.setString(5, assessment.getStaffID());
			ps.setLong(6, assessment.getAssessmentID());

			ps.execute();

		}
		finally
		{
			if (ps != null) ps.close();
		}
	}

	/**
	 * Prevent Instances of DAAssignment
	 */
	private DAAssessment()
	{
	}
}
